package com.ph.irp.base.usermanage.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.usermanage.dao.PBasePurviewFunctiontypeDAO;
import com.ph.irp.model.PBasePurviewFunctiontype;
import com.ph.util.DBUtil;

public class PBasePurviewFunctiontypeDAOImpl implements
		PBasePurviewFunctiontypeDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List<PBasePurviewFunctiontype> getFunctionTypeByFunctionParentTypeID(
			int functionParentTypeID) throws Throwable {

		List<PBasePurviewFunctiontype> resList = new ArrayList<PBasePurviewFunctiontype>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select f.FunctionTypeID fid,f.FunctionType ftype ");
			sb.append("from p_base_purview_functiontype f ");
			sb.append("where f.FunctionParentTypeID=").append(
					functionParentTypeID);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBasePurviewFunctiontype ft = new PBasePurviewFunctiontype();
				ft.setFunctionType(rs.getString("ftype"));
				ft.setFunctionTypeId(rs.getInt("fid"));
				resList.add(ft);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

		return resList;

	}

	public List<PBasePurviewFunctiontype> getAllFunctionType() throws Throwable {
		List<PBasePurviewFunctiontype> resList = new ArrayList<PBasePurviewFunctiontype>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select f.FunctionTypeID fid,f.FunctionType ftype ");
			sb.append("from p_base_purview_functiontype f ");

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBasePurviewFunctiontype ft = new PBasePurviewFunctiontype();
				ft.setFunctionType(rs.getString("ftype"));
				ft.setFunctionTypeId(rs.getInt("fid"));
				resList.add(ft);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

		return resList;

	}

	public int getFunctionParentTypeIDByFunctionTypeID(int functionTypeID)
			throws Throwable {
		int res = 0;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select f.FunctionParentTypeID fpid ");
			sb.append("from p_base_purview_functiontype f ");
			sb.append("where f.FunctionTypeID=").append(functionTypeID);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				res = rs.getInt("fpid");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

		return res;
	}

	public String getFunctionTypeDescByFunctionTypeName(String functionTypeName)
			throws Throwable {
		String res = null;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select f.FunctionTypeDesc functiontypedesc ");
			sb.append("from p_base_purview_functiontype f ");
			sb.append("where f.FunctionType like '%").append(functionTypeName)
					.append("%'");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			if (rs.next()) {
				res = rs.getString("functiontypedesc");
			} else {
				while (rs.next()) {
					res = rs.getString("functiontypedesc");
				}
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

		return res;

	}

	public String getFunctionTypeByFunctionTypeID(int functionTypeID)
			throws Throwable {
		String res = null;
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select f.FunctionType ftype ");
			sb.append("from p_base_purview_functiontype f ");
			sb.append("where f.FunctionTypeID=").append(functionTypeID);
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				res = rs.getString("ftype");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return res;
	}
}
